package dbtools;

import java.util.ArrayList;
import java.util.HashMap;

import android.annotation.TargetApi;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Build;
import android.util.Log;

public class TripDB extends SQLiteOpenHelper {

	private static final String TAG = "TripDB";

	private static final String DATABASE = "trip.db";
	private static final String TABLE_TRIP = "trip";
	private static final String TABLE_LEG = "leg";
	private static final int DATABASE_VERSION = 1;

	public TripDB(Context applicationContext) {
		super(applicationContext, DATABASE, null, DATABASE_VERSION);
	}

	@TargetApi(Build.VERSION_CODES.JELLY_BEAN)
	@Override
	public void onConfigure(SQLiteDatabase db) {
		super.onConfigure(db);
		db.setForeignKeyConstraintsEnabled(true);
	}

	/**
	 * Constructor for this class
	 */

	@Override
	public void onCreate(SQLiteDatabase db) {
		Log.d(TAG, "TripDB onCreate called");
		String query;
		query = "CREATE TABLE " + TABLE_TRIP + "(tripId INTEGER PRIMARY KEY, " +
				"tripName TEXT)";
		Log.d(TAG, query);
		db.execSQL(query);

		query = "CREATE TABLE " + TABLE_LEG + "(legId INTEGER PRIMARY KEY, "
				+ "tripId INTEGER, " + "legName TEXT, " + "legType TEXT, "
				+ "originName TEXT, " + "originType TEXT, "
				+ "originTime TEXT, " + "originDate TEXT, "
				+ "destinationName TEXT, " + "destinationType TEXT, "
				+ "destinationTime TEXT, " + "destinationDate TEXT, "
				+ "notesText TEXT, " + "journeyDetailRef TEXT, "
				+ "FOREIGN KEY(tripId) REFERENCES " + TABLE_TRIP
				+ "(tripId) ON UPDATE CASCADE ON DELETE CASCADE)";
		Log.d(TAG, query);
		db.execSQL(query);
	}

	@Override
	public void onUpgrade(SQLiteDatabase database, int oldVersion,
			int newVersion) {
		Log.d(TAG, "MemoTools onUpgrade called");

		String query = "DROP TABLE IF EXISTS " + TABLE_LEG;
		database.execSQL(query);
		query = "DROP TABLE IF EXISTS " + TABLE_TRIP;
		database.execSQL(query);

		onCreate(database);
	}

	/*
	 * Trip start
	 */

	public void insertTrip() {
		Log.d(TAG, "TripDB insertTrip called");

		SQLiteDatabase db = this.getWritableDatabase();
		
		ContentValues values = new ContentValues();

		values.put("tripName", "tripName");
		db.insert(TABLE_TRIP, null, values);

		db.close();
	}

	public ArrayList<HashMap<String, String>> getAllTrips() {
		Log.d(TAG, "TripDB getAllTrips called");

		ArrayList<HashMap<String, String>> tripList = new ArrayList<HashMap<String, String>>();

		String selectQuery = "SELECT * FROM " + TABLE_TRIP;

		Log.d(TAG, selectQuery);

		SQLiteDatabase db = this.getReadableDatabase();

		Cursor cursor = db.rawQuery(selectQuery, null);

		if (cursor.moveToFirst()) {
			do {
				HashMap<String, String> tripMap = new HashMap<String, String>();

				tripMap.put("tripId", cursor.getString(0));

				tripList.add(tripMap);
			} while (cursor.moveToNext());
		}

		cursor.close();
		db.close();

		return tripList;
	}

	/**
	 * 
	 * @param tableName
	 * @param columnName
	 * @return id
	 */
	public int getMaxId(String tableName, String columnName) {
		SQLiteDatabase db = this.getReadableDatabase();
		int id = 0;

		final String MY_QUERY = "SELECT MAX(" + columnName + ") AS _id FROM "
				+ tableName;
		Cursor mCursor = db.rawQuery(MY_QUERY, null);
		try {
			if (mCursor.getCount() > 0) {
				mCursor.moveToFirst();
				id = mCursor.getInt(0);
			}
		} catch (Exception e) {
			System.out.println(e.getMessage());
		} finally {
			db.close();
		}
		Log.d("TripList", "MaxID: " + id);
		return id;
	}

	public int getMaxId(String tableName, String columnName, String whereClause) {
		SQLiteDatabase db = this.getReadableDatabase();
		int id = 0;

		final String MY_QUERY = "SELECT MAX(" + columnName + ") AS _id FROM "
				+ tableName + " where tripId = '" + whereClause + "'";
		Cursor mCursor = db.rawQuery(MY_QUERY, null);
		try {
			if (mCursor.getCount() > 0) {
				mCursor.moveToFirst();
				id = mCursor.getInt(0);
			}
		} catch (Exception e) {
			System.out.println(e.getMessage());
		} finally {
			db.close();
		}
		return id;
	}

	/*
	 * Leg Start
	 */

	public void insertLeg(HashMap<String, String> queryValues) {
		Log.d(TAG, "TripDB insertLeg called");

		SQLiteDatabase db = this.getWritableDatabase();

		ContentValues values = new ContentValues();

		values.put("tripId", queryValues.get("tripId"));
		values.put("legName", queryValues.get("legName"));
		values.put("legType", queryValues.get("legType"));
		values.put("originName", queryValues.get("originName"));
		values.put("originType", queryValues.get("originType"));
		values.put("originTime", queryValues.get("originTime"));
		values.put("originDate", queryValues.get("originDate"));
		values.put("destinationName", queryValues.get("destinationName"));
		values.put("destinationType", queryValues.get("destinationType"));
		values.put("destinationTime", queryValues.get("destinationTime"));
		values.put("destinationDate", queryValues.get("destinationDate"));
		values.put("notesText", queryValues.get("notesText"));
		values.put("journeyDetailRef", queryValues.get("journeyDetailRef"));

		db.insert(TABLE_LEG, null, values);

		db.close();
	}

	public ArrayList<HashMap<String, String>> getAllLegsByTripId(int tripId) {
		ArrayList<HashMap<String, String>> legList = new ArrayList<HashMap<String, String>>();

		String selectQuery = "SELECT * FROM " + TABLE_LEG + " WHERE tripId = '"
				+ tripId + "'";

		SQLiteDatabase db = this.getWritableDatabase();

		Cursor cursor = db.rawQuery(selectQuery, null);

		if (cursor.moveToFirst()) {

			do {

				HashMap<String, String> legMap = new HashMap<String, String>();

				legMap.put("legId", cursor.getString(0));
				legMap.put("tripId", cursor.getString(1));
				legMap.put("legName", cursor.getString(2));
				legMap.put("legType", cursor.getString(3));
				legMap.put("originName", cursor.getString(4));
				legMap.put("originType", cursor.getString(5));
				legMap.put("originTime", cursor.getString(6));
				legMap.put("originDate", cursor.getString(7));
				legMap.put("destinationName", cursor.getString(8));
				legMap.put("destinationType", cursor.getString(9));
				legMap.put("destinationTime", cursor.getString(10));
				legMap.put("destinationDate", cursor.getString(11));
				legMap.put("notesText", cursor.getString(12));
				legMap.put("journeyDetailRef", cursor.getString(13));

				legList.add(legMap);
			} while (cursor.moveToNext());
		}
		return legList;
	}
}
